package org.example.utils;


import com.alibaba.fastjson2.JSONObject;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class POIUtils {
    /**
     * 获取workbook
     */
    public static Workbook getWorkbook(InputStream inputStream, String fileName) throws IOException {

        boolean isXlsx = fileName.endsWith(".xlsx");

        try {
            return isXlsx ? new XSSFWorkbook(inputStream) : new HSSFWorkbook(inputStream);
        } catch (Exception e) {
            return isXlsx ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
        }
    }

    public static Workbook getWorkbook(InputStream inputStream) throws IOException {

        try {
            return new HSSFWorkbook(inputStream);
        } catch (Exception e) {
            return new XSSFWorkbook(inputStream);
        }
    }

    /**
     * 获取sheet
     */
    public static Sheet getSheet(Workbook workbook, Integer index) {
        return workbook.getSheetAt(index);
    }

    /**
     * 获取cell
     */
    public static Cell getCell(Sheet sheet, int row, int col) {
        return sheet.getRow(row).getCell(col);
    }

    /**
     * 获取value
     */
    public static String getCellValue(Cell cell) {
        return switch (cell.getCellType()) {
            case STRING -> cell.getStringCellValue();
            case NUMERIC -> String.valueOf(cell.getNumericCellValue());
            case BOOLEAN -> String.valueOf(cell.getBooleanCellValue());
            default -> null;
        };
    }

    public static String getCellValue(Sheet sheet, MergeCell mergeCell) {

        // 如果不是合并单元格，直接返回该单元格的内容
        if (Objects.equals(mergeCell.getStartRow(), mergeCell.getEndRow()) && Objects.equals(mergeCell.getStartCol(), mergeCell.getEndCol())) {
            Cell cell = getCell(sheet, mergeCell.getStartRow(), mergeCell.getStartCol());
            return getCellValue(cell);
        }

        // 如果是合并单元格，获取合并区域的第一个非空单元格的内容
        Cell cell = getMergedCell(sheet, mergeCell.getStartRow(), mergeCell.getStartCol(), mergeCell.getEndRow(), mergeCell.getEndCol());
        return null == cell ? null : getCellValue(cell);
    }

    public static Cell getMergedCell(Sheet sheet, int startRow, int startCol, int endRow, int endCol) {

        // 遍历所有合并区域
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {

            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            // 检查合并区域是否与指定的行列范围匹配
            if (mergedRegion.getFirstRow() != startRow || mergedRegion.getFirstColumn() != startCol
                    || mergedRegion.getLastRow() != endRow || mergedRegion.getLastColumn() != endCol) {
                continue;
            }

            // 遍历合并区域内的所有单元格，获取首个非空单元格的内容
            for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                for (int colIndex = startCol; colIndex <= endCol; colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    if (cell == null || cell.getCellType() == CellType.BLANK) {
                        continue;
                    }
                    return cell;
                }
            }
        }

        return null;
    }

    public static Cell getMergedRangeCell(Sheet sheet, int startRow, int startCol, int endRow, int endCol) {

        // 遍历所有合并区域
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {

            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
            // 检查合并区域是否与指定的行列范围匹配
            if (mergedRegion.getFirstRow() != startRow || mergedRegion.getFirstColumn() != startCol
                    || mergedRegion.getLastRow() != endRow || mergedRegion.getLastColumn() != endCol) {
                continue;
            }

            // 遍历合并区域内的所有单元格，获取首个非空单元格的内容
            for (int rowIndex = startRow; rowIndex <= endRow; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                for (int colIndex = startCol; colIndex <= endCol; colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    /*if (cell == null || cell.getCellType() == CellType.BLANK) {
                        continue;
                    }*/
                    return cell;
                }
            }
        }

        return null;
    }

    public static String incrementCellReference(String cellReference) {
        return incrementCellReference(cellReference, 1);
    }

    public static void main(String[] args) {
        System.out.println(getCellsInRange("A4","DY4").size());
    }

    public static String incrementCellReference(String cellReference, int increment) {
        StringBuilder columnPart = new StringBuilder();
        StringBuilder rowPart = new StringBuilder();

        // 将字母部分和数字部分分离
        for (char ch : cellReference.toCharArray()) {
            if (Character.isLetter(ch)) {
                columnPart.append(ch);
            } else if (Character.isDigit(ch)) {
                rowPart.append(ch);
            }
        }

        // 解析行号并增加指定的增量
        int rowNumber = Integer.parseInt(rowPart.toString()) + increment;

        // 生成新的单元格引用
        return columnPart.toString() + rowNumber;
    }

    public static RowAndColumnFromCellReference getRowAndColumnFromCellReference(String cellReference) {
        int rowNumber = 0;
        int columnNumber = 0;

        // 提取字母部分和数字部分
        StringBuilder columnPart = new StringBuilder();
        StringBuilder rowPart = new StringBuilder();

        for (char ch : cellReference.toCharArray()) {
            if (Character.isLetter(ch)) {
                columnPart.append(ch);
            } else if (Character.isDigit(ch)) {
                rowPart.append(ch);
            }
        }

        // 计算列号
        for (int i = 0; i < columnPart.length(); i++) {
            columnNumber = columnNumber * 26 + (Character.toUpperCase(columnPart.charAt(i)) - 'A' + 1);
        }

        // 列号从0开始，所以需要减去1
        columnNumber -= 1;

        // 计算行号
        rowNumber = Integer.parseInt(rowPart.toString()) - 1; // 行号从0开始，所以需要减去1

        RowAndColumnFromCellReference rowAndColumnFromCellReference = new RowAndColumnFromCellReference();
        rowAndColumnFromCellReference.setRowNumber(rowNumber);
        rowAndColumnFromCellReference.setColumnNumber(columnNumber);
        return rowAndColumnFromCellReference;
    }

    // 工具方法，返回指定范围内的所有单元格
    public static List<String> getCellsInRange(String startCell, String endCell) {
        List<String> cells = new ArrayList<>();
        int[] start = getCellPosition(startCell);
        int[] end = getCellPosition(endCell);

        int startRow = start[1];
        int endRow = end[1];
        int startCol = start[0];
        int endCol = end[0];

        if (startRow != endRow) {
            throw new IllegalArgumentException("Start cell and end cell must be in the same row.");
        }

        for (int col = startCol; col <= endCol; col++) {
            String cellAddress = getCellAddress(col, startRow);
            cells.add(cellAddress);
        }

        return cells;
    }

    // 将单元格地址转换为列号和行号
    private static int[] getCellPosition(String cell) {
        int col = 0;
        int row = 0;
        int i = 0;

        // 解析列号
        while (i < cell.length() && Character.isLetter(cell.charAt(i))) {
            col = col * 26 + (cell.charAt(i) - 'A' + 1);
            i++;
        }

        // 解析行号
        while (i < cell.length() && Character.isDigit(cell.charAt(i))) {
            row = row * 10 + (cell.charAt(i) - '0');
            i++;
        }

        return new int[]{col - 1, row - 1};  // 返回0基索引
    }

    // 将列号和行号转换为单元格地址
    private static String getCellAddress(int col, int row) {
        StringBuilder sb = new StringBuilder();

        col++;  // 转换为1基索引
        while (col > 0) {
            col--;
            sb.insert(0, (char) ('A' + col % 26));
            col /= 26;
        }

        row++;  // 转换为1基索引
        sb.append(row);

        return sb.toString();
    }


    @Data
    public static class MergeCell {
        private Integer startRow;
        private Integer startCol;
        private Integer endRow;
        private Integer endCol;
    }

    @Data
    public static class RowAndColumnFromCellReference {
        private Integer rowNumber;
        private Integer columnNumber;

    }


}
